package com.young.shop_admin.dao.impl;



import com.young.shop_admin.dao.GoodsDao;
import com.young.shop_admin.pojo.entity.Goods;
import com.young.shop_admin.utils.DBUtils;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 *
 */

public class GoodsDaoImpl implements GoodsDao {
    //创建自动化管理工具
    private static QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());
    //开启驼峰映射
    private static BeanProcessor bean = new GenerousBeanProcessor();
    private static RowProcessor processor = new BasicRowProcessor(bean);

    @Override
    public List<Goods> getAllGoods() {
        //执行的SQL语句
        String sql = "SELECT * FROM a_goods";
        //定义返回形式
        BeanListHandler<Goods> goodsBeanListHandler = new BeanListHandler<>(Goods.class, processor);
        //执行sql
        try {
            List<Goods> query = queryRunner.query(sql, goodsBeanListHandler);

            return query;

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public int updateGoodsById(int id, String status) {
        //执行的sql语句
        String sql = "UPDATE a_goods SET `status` = ?,update_time = ? WHERE ID = ?";
        //获取当前时间
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateFormat = simpleDateFormat.format(new Date());
        //参数封装
        Object[] params = {status,dateFormat,id};
        //执行sql
        try {
            return queryRunner.execute(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public Goods queryGoodsById(int id) {
        //执行的sql语句
        String sql = "SELECT * FROM a_goods WHERE id = ?";
        //定义返回的类型
        BeanHandler<Goods> goodsBeanHandler = new BeanHandler<>(Goods.class, processor);
        //执行sql
        try {
            return queryRunner.query(sql,goodsBeanHandler,id);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Goods> findAllGoods() throws SQLException {
        //查询所有的商品信息
        //执行的sql语句
        String sql = "SELECT * FROM a_goods";

        //把商品到的商品,转为list集合,泛型为Goods
        List<Goods> query = queryRunner.query(sql, new BeanListHandler<>(Goods.class));
        return query;
    }


    @Override
    public List<Goods> findGoods(int currentPage, int pageSize) throws SQLException {
        //执行的sql语句
        String sql = "select * from a_goods limit ?,?";

        //limit第一个参数:从数据库中的哪里开始查,索引是从0开始的
        //第二个参数:每次查多少个
        //第一个参数的规律是:当前页数减一,乘以每页查询的个数
        BeanListHandler<Goods> goodsBeanListHandler = new BeanListHandler<>(Goods.class, processor);
        //参数封装
        Object[] params = {(currentPage-1)*pageSize,pageSize};
        List<Goods> query = queryRunner.query(sql, goodsBeanListHandler, params);
        //List<Goods> query = queryRunner.query(sql, new BeanListHandler<>(Goods.class), (currentPage - 1) * pageSize, pageSize);
        return query;
    }

    @Override
    public int findTotalCount() throws SQLException {
        //执行的sql语句
        String sql = "select count(*) from a_goods";
        Long countL = (Long) queryRunner.query(sql, new ScalarHandler<>());

        return countL.intValue();
    }

    @Override
    public List<Goods> getGoodsByType(String type) {
        //执行的sql语句
        String sql = "SELECT * FROM a_goods WHERE type = ?";
        //定义返回的类型
        BeanListHandler<Goods> goodsBeanListHandler = new BeanListHandler<>(Goods.class, processor);
        //执行sql
        try {
            List<Goods> query = queryRunner.query(sql, goodsBeanListHandler, type);
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Goods> getGoodsTypePage(int currentPage, int pageSize, String type) {

        //执行的sql语句
        String sql = "SELECT * FROM a_goods WHERE type = ? limit ?, ?";
        //定义返回的类型
        BeanListHandler<Goods> goodsBeanListHandler = new BeanListHandler<>(Goods.class, processor);
        //执行sql
        Object[] params = {type,(currentPage-1)*pageSize,pageSize};
        try {
            List<Goods> query = queryRunner.query(sql, goodsBeanListHandler, params);
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
    }

    @Override
    public int getOnSaleGoodsNumber() {
        //执行的sql语句
        String sql = "select count(*) from a_goods WHERE status = '热售中'";
        Long countL =0L;
        try {
            countL =  (Long)queryRunner.query(sql, new ScalarHandler<>());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return countL.intValue();
    }

}
